﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

namespace System
{
    public static class DataBaseEx
    {
        /// <summary>
        /// 获取序列号
        /// </summary>
        /// <returns></returns>
        private static int GetSequence(this Database db)
        {
            var sql = @"if exists (select 1 from Sequence as s where DATEDIFF(day,GETDATE(),s.CreateTime)= 0)
                          update Sequence set SequenceNo = SequenceNo + 1 where DATEDIFF(day,GETDATE(),CreateTime)= 0
                       else 
                          insert into Sequence values(NEWID(),1,GETDATE())
                       select SequenceNo from Sequence where DATEDIFF(day,GETDATE(),CreateTime)= 0";

            return db.SqlQuery<int>(sql).FirstOrDefault();
        }

        /// <summary>
        /// 客户编号
        /// </summary>
        /// <returns></returns>
        public static string NewCustormerNo(this Database db)
        {
            return string.Format("M{0}{1}", DateTime.Now.ToString("yyyyMMdd"), db.GetSequence().ToString().PadLeft(6, '0'));
        }

        #region 动态执行Sql语句扩展
        /// <summary>
        /// 执行Sql查询       
        /// <example>SqlQuery("select * from [Table] where name={0} and password={1}", "abc", "123456")</example>
        /// </summary>
        /// <param name="database">数据库</param>
        /// <param name="sql">sql语句</param>
        /// <param name="values">值或参数[可空]</param>       
        /// <exception cref="ArgumentException"></exception>
        /// <returns></returns>
        public static DataTable SqlQuery(this Database database, string sql, params object[] values)
        {
            using (SqlConnection con = new SqlConnection(database.Connection.ConnectionString))
            {
                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }

                var parameters = GetSqlParameters(ref sql, values);
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    cmd.Parameters.AddRange(parameters);
                    using (SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd))
                    {
                        using (DataSet dataSet = new DataSet())
                        {
                            dataAdapter.Fill(dataSet);
                            return dataSet.Tables[0];
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 根据sql语句和参数的值动态生成Sqlparameter
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="values">值或参数[可空]</param>        
        /// <exception cref="ArgumentException"></exception>
        /// <returns></returns>
        private static SqlParameter[] GetSqlParameters(ref string sql, params object[] values)
        {
            if (values == null)
            {
                return new SqlParameter[] { };
            }
            var parameters = sql.Matches(@"\{\d+\}").Distinct().OrderBy(item => item).ToArray();
            if (values.Length < parameters.Count())
            {
                throw new ArgumentException("values的元素数目和Sql语句不匹配");
            }

            var result = new SqlParameter[parameters.Length];
            for (var i = 0; i < parameters.Length; i++)
            {
                var pName = "@p" + i.ToString();
                sql = sql.Replace("{" + i.ToString() + "}", pName);
                result[i] = new SqlParameter(pName, values[i]);
            }
            return result;
        }
        #endregion
    }
}
